<?php

declare(strict_types=1);

namespace app\admin\service;

use app\admin\model\Customer;
use app\admin\service\CustomerLog as CustomerLogService;
use app\admin\model\ExpandCustomer as ExpandCustomerModel;
use app\common\RedisClient;
use think\facade\Db;
use think\facade\Log;

class ExpandCustomer extends \think\Service
{
    /**
     * 注册服务
     *
     * @return mixed
     */
    public function register()
    {
        //
    }

    /**
     * 执行服务
     *
     * @return mixed
     */
    public function boot()
    {
        //
    }

    /**
     * 获取列表数据
     * @param $param
     * @param $page
     * @param $limit
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * 2024-10-23 15:53:03
     * author: jiang
     */
    public function index($param, $page, $limit)
    {
        $arrData = ['code' => 0, 'message' => 'success'];
        $where = [];
        if ($param['country']) {
            $where[] = ['country', 'like', '%' . $param['country'] . '%'];
        }
        if ($param['city']) {
            $where[] = ['city', 'like', '%' . $param['city'] . '%'];
        }
        if ($param['keywords']) {
            $where[] = ['keywords', 'like', '%' . $param['keywords'] . '%'];
        }
        if ($param['source']) {
            $where[] = ['source', 'like', '%' . $param['source'] . '%'];
        }

        if ($param['start_time']) {
            $where[] = ['create_time', '>=', $param['start_time']];
        }
        if ($param['end_time']) {
            $where[] = ['create_time', '<', $param['end_time']];
        }

        $oExpandCustomerModel = ExpandCustomerModel::where($where)->append(['user_link']);
        $total = $oExpandCustomerModel->count();
        $result = $oExpandCustomerModel->page($page, $limit)->order('last_crawl_time', 'desc')->select();
        $arrData['data'] = ['list' => $result->toArray(), 'total' => $total];
        return $arrData;
    }

    /**
     * 获取单条信息
     * @param $param
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * 2024-10-23 15:55:36
     * author: jiang
     */
    public function read($param)
    {
        $where = [['id', '=', $param['id']]];
        $oExpandCustomerModel = ExpandCustomerModel::where($where)->append(['items_value', 'user_link'])->find();
        if (!$oExpandCustomerModel) {
            return ['code' => 1, 'message' => '获取信息失败！'];
        }
        return ['code' => 0, 'message' => 'success', 'data' => $oExpandCustomerModel];
    }

    /**
     * @describe 同步客户ID，社媒渠道抓到的用户，同步到客户表中
     * @author: Bobo
     * @date: 2024-10-28 17:28:19 
     * @param limit  [int] @required
     * @return Renderable
     */
    public function synCustomerId(int $limit = 20)
    {
        $customerModel = new Customer();
        $customerLogService = new CustomerLogService();
        $successCount = 0;  // 添加计数器

        $waitingSyncData = ExpandCustomerModel::whereNull('customer_id')->limit($limit)->select()->toArray();

        try {
            // 预先获取所有需要处理的国家数据，避免循环中重复查询
            $countryNameArr = array_unique(array_map('strtoupper', array_column($waitingSyncData, 'country')));
            $countries = Db::name('region_country')
                ->where(function ($query) use ($waitingSyncData, $countryNameArr) {
                    $query->whereIn('country_code', $countryNameArr)
                        ->whereOr(function ($query) use ($countryNameArr) {
                            $query->whereIn('name', $countryNameArr);
                        });;
                })
                ->column('*');

            // 批量插入准备
            $customerBatchData = [];
            $expandCustomerUpdates = [];

            foreach ($waitingSyncData as $value) {
                $countryId = 0;
                foreach ($countries as $item) {
                    if ($item['name'] == $value['country'] || $item['country_code'] == $value['country']) {
                        $countryId = $item['id'];
                        break;
                    }
                }

                // 数据验证
                if (empty($value['user_id'])) {
                    Log::warning('同步客户数据缺失: ' . json_encode($value, JSON_UNESCAPED_UNICODE));
                    continue;
                }

                $customerBatchData[] = [
                    'cusname' => $value['user_name'] ?? '',
                    'tripartite_channel_id' => $value['source'] ?? '',
                    'tripartite_account' => $value['user_id'],
                    'country_id' => $countryId,
                    'remark' => $value['keywords'],
                    'cusnature' => 99,
                    'source' => 3,
                    'is_login' => 2,
                    'login_status' => 2,
                    'create_time' => date('Y-m-d H:i:s'),
                ];

                $expandCustomerUpdates[] = $value['id'];
            }

            if (empty($customerBatchData)) {
                return ['code' => 0, 'message' => '没有需要同步的数据'];
            }

            // 记录插入时间
            $insertTime = date('Y-m-d H:i:s');

            $affectedRows = $customerModel->insertAll($customerBatchData);

            if ($affectedRows !== count($customerBatchData)) {
                throw new \Exception('批量插入数据不完整，预期插入' . count($customerBatchData) . '条，实际插入' . $affectedRows . '条');
            }

            // 获取最后插入的ID
            $lastId = $customerModel->getLastInsID();

            // 获取批量插入的ID范围
            $insertIds = $customerModel->where('id', '>=', $lastId - $affectedRows + 1)
                ->where('id', '<=', $lastId)
                ->order('id', 'asc')
                ->column('id');

            // 验证ID数量是否匹配
            if (count($insertIds) !== $affectedRows) {
                throw new \Exception('插入ID数量与数据不匹配');
            }

            // 批量记录日志
            foreach ($insertIds as $customerId) {
                $customerLogService->logging('创建用户', $customerId, '系统抓取');
            }

            // 批量更新扩展客户表
            // ExpandCustomerModel::whereIn('id', $expandCustomerUpdates)
            //     ->update([
            //         'customer_id' => Db::raw('(SELECT id FROM db_customer WHERE tripartite_account  = db_expand_customer.user_id )')
            //     ]);
            $expand_customer = Db::name('customer')
                ->alias('a')
                ->join('db_expand_customer b', 'a.tripartite_account = b.user_id')
                ->where('a.is_del', 0)
                ->whereNull('b.customer_id')
                ->field('a.id, b.user_id')
                ->select()
                ->toarray();

            foreach ($expand_customer as $key => $cust) {
                Db::name('expand_customer')->where('user_id', $cust['user_id'])->update(['customer_id' => $cust['id']]);
            }



            $successCount = count($insertIds);
            $time = date('Y-m-d H:i:s');
            Db::commit();

            return ['code' => 0, 'message' => "同步成功，{$time}共同步 {$successCount} 条数据"];
        } catch (\think\db\exception\DbException $e) {
            Db::rollback();
            Log::error('数据库操作异常: ' . $e->getMessage() . 'line:' . $e->getLine());
            return ['code' => 500001, 'message' => '数据库操作异常，请检查日志'];
        } catch (\Exception $e) {
            Db::rollback();
            Log::error('同步客户数据异常: ' . $e->getMessage() . ' line:' . $e->getLine());
            return ['code' => 500001, 'message' => '系统异常，请检查日志'];
        }

        // // 将整个批次放在一个事务中
        // Db::startTrans();
        // try {
        //     foreach ($waitingSyncData as $key => $value) {
        //         $country = Db::name('region_country')->where([
        //             'country_code' => strtoupper($value['country'])
        //         ])->whereOr([
        //             'full_name',
        //             'like',
        //             '%' . $value['country'] . '%'
        //         ])->field('id')->find();
        //         // 创建新客户记录
        //         $customerData = [
        //             'cusname' => $value['user_name'] ?? '',
        //             'tripartite_channel_id' =>  $value['source'] ?? '',
        //             'tripartite_account' => $value['user_id'] ?? '',
        //             'country_id' => $country['id'] ?? $value['country'],
        //             'remark' => $value['keywords'],
        //             'cusnature' => 99,
        //             'source' => 3,
        //             'is_login' => 2,
        //             'login_status' => 2,
        //             'create_time' => date('Y-m-d H:i:s'),
        //         ];

        //         $customerId = $customerModel->insertGetId($customerData);
        //         $customerLogService->logging('创建用户', $customerId, '系统抓取');
        //         // 更新扩展客户表的customer_id
        //         ExpandCustomerModel::where('id', $value['id'])
        //             ->update(['customer_id' => $customerId]);

        //         $successCount++;  // 成功一条就计数加1
        //     }
        //     $time = date('Y-m-d H:i:s', time());
        //     Db::commit();
        //     return ['code' => 0, 'message' => "同步成功，{$time}共同步 {$successCount} 条数据"];
        // } catch (\Exception $e) {
        //     Db::rollback();
        //     $time = date('Y-m-d H:i:s', time());
        //     return ['code' => 500001, 'message' => "同步失败：{$time}" . $e->getMessage()];
        // }
    }
}
